package modelovectorial.persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import modelovectorial.DocumentoPDF;
import modelovectorial.Palabra;
import modelovectorial.core.SuggestResult;
import soporte.MySQLConnexion;
import utn.frc.tsb.utils.ValerioFritelli.HashTable.HashList;
import utn.frc.tsb.utils.ValerioFritelli.SimpleList.SimpleList;

/**
 * @project DLC Final / UTN FRC 2013
 * @date Febrero de 2013
 *
 * @author Gonzalo Arribere
 * @author Gustavo Morales
 * @author Pablo Moreno
 */
public class SuggestResultDB {

    private final static String GETDOCUMENTSCOUNT = ""
            + "SELECT d.idDocumento, d.maxTF, COUNT(*) as DocumentWords "
            + "FROM documento AS d, termino AS t, indices AS i, terminosucio AS s "
            + "WHERE "
            + "   i.idDocumento = d.idDocumento AND    "
            + "   i.idTerminoSucio = s.idTerminoSucio AND    "
            + "   s.idTermino = t.idTermino AND    "
            + "   t.terminoLimpio IN (w?) "
            + "GROUP BY d.idDocumento "
            + "HAVING DocumentWords >= ?";
    private final static String GETDOCUMENTS = ""
            + "SELECT d.idDocumento, i.indice, t.terminoLimpio, t.nr, p.tf, t.idTermino "
            + "FROM documento AS d, termino AS t, indices AS i, terminosucio AS s, posteo AS p  "
            + "WHERE  "
            + "   p.idDocumento = d.idDocumento AND "
            + "   p.idTermino = t.idTermino AND "
            + "   i.idDocumento = d.idDocumento AND     "
            + "   i.idTerminoSucio = s.idTerminoSucio AND   "
            + "   s.idTermino = t.idTermino AND     "
            + "   t.terminoLimpio IN (w?)  AND "
            + "   i.idDocumento IN (i?) "
            + "ORDER BY d.idDocumento ASC, i.indice ASC";
    private final static String GET_WORDS_SUCIAS = ""
            + "SELECT termino, idTerminoSucio "
            + "FROM terminosucio "
            + "WHERE termino IN (?)";
    private final static String GET_FIST_WORD = ""
            + "SELECT idDocumento, indice "
            + "FROM indices "
            + "WHERE idTerminoSucio = ? "
            + "GROUP BY idDocumento "
            + "LIMIT 50";
    private final static String GET_SECOND_WORD = ""
            + "SELECT COUNT(*) as Total "
            + "FROM indices "
            + "WHERE idDocumento = ? "
            + "AND idTerminoSucio = ? "
            + "AND indice = ? ";

    /**
     * Construye un PreparedStatement, añadiendo en la Consulta tantos "?" como
     * elementos tenga la SimpleList (con terminos de consulta)
     *
     * @param pQuery
     * @param pQueryTerms
     * @param pConnection
     * @return
     */
    public static PreparedStatement buildPreparedStatement(String pQuery, SimpleList pQueryTerms, Connection pConnection) {
        int length = pQueryTerms.size();

        StringBuilder queryItems = new StringBuilder("?");

        for (int i = 1; i < length; i++) {
            queryItems.append(",?");
        }

        String query = pQuery.replace("w?", queryItems.toString());
        String queryTerm;
        PreparedStatement preparedStatment = null;

        try {
            preparedStatment = pConnection.prepareStatement(query);

            pQueryTerms.startIterator();
            int index = 1;
            while (pQueryTerms.hasNext()) {
                queryTerm = (String) pQueryTerms.next();
                preparedStatment.setString(index, queryTerm);
                preparedStatment.setString(index, queryTerm);
                index++;
            }
        } catch (SQLException e) {
            Logger.getLogger(SuggestResultDB.class.getName()).log(Level.SEVERE, null, e);
        }
        return preparedStatment;
    }

    /**
     * Retorna un array de int's con todos los elementos de una simpleList
     *
     * @param pQueryTerms
     * @return array []
     */
    public static int[] arrayHashWords(SimpleList pQueryTerms) {
        int array[] = new int[pQueryTerms.size()];
        int Index = 0;
        pQueryTerms.startIterator();

        while (pQueryTerms.hasNext()) {
            array[Index] = pQueryTerms.next().hashCode();
            Index++;
        }
        return array;
    }

    /**
     * Extiende la Consulta GETDOCUMENTS con tantos "?" como lo espefique el
     * parametro int pCount
     *
     * @param pCount
     * @return
     */
    public static String extendQueryParams(int pCount) {
        StringBuilder queryItems = new StringBuilder("?");
        for (int i = 1; i < pCount; i++) {
            queryItems.append(",?");
        }
        return GETDOCUMENTS.replace("i?", queryItems.toString());
    }

    public static HashList findTerminosSucios(SimpleList pQueryTerms) {
        HashList terminos = new HashList(20);

        int length = pQueryTerms.size();

        StringBuilder queryItems = new StringBuilder("?");
        for (int i = 1; i < length; i++) {
            queryItems.append(",?");
        }
        String query = GET_WORDS_SUCIAS.replace("?", queryItems.toString());

        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;

        Palabra word;
        try {
            PreparedStatement preparedStatment = connection.prepareStatement(query);
            System.out.println(preparedStatment);

            int index = 1;
            String queryTerm;

            pQueryTerms.startIterator();
            while (pQueryTerms.hasNext()) {
                queryTerm = (String) pQueryTerms.next();
                preparedStatment.setString(index, queryTerm);
                index++;
            }

            result = preparedStatment.executeQuery();
            while (result.next()) {
                queryTerm = result.getString("termino");
                pQueryTerms.startIterator();
                while (pQueryTerms.hasNext()) {
                    if (((String) pQueryTerms.next()).compareTo(queryTerm) == 0) {
                        word = new Palabra(result.getInt("idTerminoSucio"), result.getString("termino"));
                        terminos.put(word);
                        break;
                    }
                }
            }
            result.close();
        } catch (Exception e) {
            Logger.getLogger(SuggestResultDB.class.getName()).log(Level.SEVERE, null, e);
        }
        return terminos;
    }

    public static DocumentoPDF[] executeQuerySingle(SimpleList pQueryTerms) {
        HashList terminos = findTerminosSucios(pQueryTerms);
        System.out.println("resultados: " + terminos.size());
        if (terminos.size() != pQueryTerms.size()) {
            return new DocumentoPDF[0];
        }

        HashList documentos = new HashList(200);
        DocumentoPDF realDocumentos[] = new DocumentoPDF[SuggestResult.MAX_SUGGEST];

        DocumentoPDF documento;

        pQueryTerms.startIterator();

        String firstWord = (String) pQueryTerms.next();
        Palabra word = (Palabra) terminos.get(new Palabra(0, firstWord));

        Palabra word2 = (Palabra) terminos.get(new Palabra(0, (String) pQueryTerms.next()));

        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;

        int i = 0;
        try {
            PreparedStatement preparedStatment = connection.prepareStatement(GET_FIST_WORD);

            preparedStatment.setInt(1, word.getIdPalabra());

            result = preparedStatment.executeQuery();
            while (result.next()) {

                documento = new DocumentoPDF(result.getInt("idDocumento"));
                documento.setMinIndex(result.getInt("indice"));
                documentos.put(documento);
            }
            result.close();

            preparedStatment = connection.prepareStatement(GET_SECOND_WORD);
            documentos.startIterator();
            while (documentos.hasNext()) {
                documento = (DocumentoPDF) documentos.next();
                preparedStatment.setInt(1, documento.getIdDocument());
                preparedStatment.setInt(2, word2.getIdPalabra());
                preparedStatment.setInt(3, documento.getMinIndex() + 1);


                result = preparedStatment.executeQuery();
                if (result.next() && result.getInt("total") == 1) {
                    realDocumentos[i] = documento;
                    i++;
                }
                result.close();

                if (i == SuggestResult.MAX_SUGGEST) {
                    break;
                }
            }
        } catch (Exception e) {
            Logger.getLogger(SuggestResultDB.class.getName()).log(Level.SEVERE, null, e);
        }
        DocumentoPDF realResults[];

        if (i != SuggestResult.MAX_SUGGEST) {
            realResults = new DocumentoPDF[i];
            System.arraycopy(realDocumentos, 0, realResults, 0, i);
        } else {
            realResults = realDocumentos;
        }
        return realResults;
    }

    /**
     * Ejecuta la consulta en la DB y retorna un array de documentos - el
     * documento solo contiene info basica (idDocumento, maxTF) y los terminos
     * que coincidan con la lista de terminos pasada por parametro. - de cada
     * termino coincidente de los documentos se lee de la DB solamente: - tf,
     * idTermino, nr y su indice
     *
     * @param pQueryTerms
     * @return
     */
    public static DocumentoPDF[] executeQuery(SimpleList pQueryTerms) {

        HashList hashList = new HashList(100);
        int arrayHashWords[] = arrayHashWords(pQueryTerms);

        int index;
        int length = pQueryTerms.size();
        int documentIndex = 0;

        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;

        DocumentoPDF results[] = null;
        DocumentoPDF document = null;
        try {
            PreparedStatement preparedStatment = buildPreparedStatement(GETDOCUMENTSCOUNT, pQueryTerms, connection);
            preparedStatment.setInt(length + 1, length);

            StringBuilder queryItems = new StringBuilder();

            result = preparedStatment.executeQuery();

            System.out.println(preparedStatment);

            while (result.next()) {
                hashList.put(new DocumentoPDF(result.getInt("idDocumento"), result.getInt("maxTF")));
                queryItems.append("?,");
            }
            result.close();
            queryItems.deleteCharAt(queryItems.length() - 1);

            if (hashList.size() != 0) {
                results = new DocumentoPDF[hashList.size()];

                preparedStatment = buildPreparedStatement(extendQueryParams(hashList.size()), pQueryTerms, connection);

                index = pQueryTerms.size() + 1;
                hashList.startIterator();
                while (hashList.hasNext()) {
                    document = (DocumentoPDF) hashList.next();
                    preparedStatment.setInt(index, document.getIdDocument());
                    index++;
                }

                result = preparedStatment.executeQuery();
                System.out.println(preparedStatment);
                index = 0;
                int idDocument = 0;
                int wordCount = 0;

                int lastIndice = 0;
                boolean ignoreNextWords = true;

                while (result.next()) {
                    if (idDocument != result.getInt("idDocumento")) {
                        idDocument = result.getInt("idDocumento");
                        document = (DocumentoPDF) hashList.get(new DocumentoPDF(idDocument));
                        index = 0;
                        lastIndice = -1;
                        ignoreNextWords = false;
                        wordCount = 0;
                    }

                    if (ignoreNextWords) {
                        continue;
                    }

                    if (lastIndice == -1) {
                        if (result.getString("terminoLimpio").hashCode() == arrayHashWords[0]) {
                            lastIndice = result.getInt("indice");
                            index = 1;
                            wordCount++;
                            document.clearTerms();
                            document.addPalabra(result.getInt("idTermino"), result.getInt("tf"), result.getInt("nr"));
                        }
                    } else if (lastIndice == (result.getInt("indice") - 1)
                            && arrayHashWords[index] == result.getString("terminoLimpio").hashCode()) {
                        index++;
                        lastIndice = result.getInt("indice");
                        wordCount++;
                        document.addPalabra(result.getInt("idTermino"), result.getInt("tf"), result.getInt("nr"));
                    } else if (result.getString("terminoLimpio").hashCode() == arrayHashWords[0]) {
                        lastIndice = result.getInt("indice");
                        index = 1;
                        wordCount = 1;
                        document.clearTerms();
                        document.addPalabra(result.getInt("idTermino"), result.getInt("tf"), result.getInt("nr"));
                    }

                    if (wordCount == length) {
                        ignoreNextWords = true;
                        document.setMinIndex(lastIndice - wordCount + 1);
                        results[documentIndex] = document;
                        documentIndex++;
                    }
                    if (documentIndex == SuggestResult.MAX_SUGGEST) {
                        break;
                    }
                }
                result.close();
            }
        } catch (Exception e) {
            Logger.getLogger(SuggestResultDB.class.getName()).log(Level.SEVERE, null, e);
        }
        DocumentoPDF realResults[] = new DocumentoPDF[documentIndex];
        System.arraycopy(results, 0, realResults, 0, documentIndex);
        return realResults;
    }
}